import unittest
import openpyxl
from openpyxl.styles import PatternFill
import pandas
import pymysql

class ReadDataTest(unittest.TestCase):

    """
    测试读取csv文件
    """
    def test_read_csv(self):
        # 读取时会自动跳过第一行, 设置未header
        data_source = pandas.read_csv(r"../openpyxl/data/a.csv")
        row_list = data_source.values.tolist()

        print(f"行读取结果：{type(row_list)}")
        print("总行数: ", len(row_list))

        for item in row_list:
            print(item[0], item[1], item[2])

    """
    测试读取指定分隔符且没有header的txt文件
    """
    def test_read_txt(self):
        # 读取txt文件
        data_source = pandas.read_table(
            # 指定读取的文件
            r"./data/a.txt",
            # 设置分隔符
            sep="|",
            # 如果没有header的话
            header=None,
            # 没有header需要手工指定
            names=["id", "name", "age", "address"]
        )
        row_list = data_source.values.tolist()
        for item in row_list:
            print(item[0], item[1], item[2])
        # shape存储读取到的行数和列数信息: (5, 4)
        print(data_source.shape, data_source.shape[0], data_source.shape[1])
        print("行数", data_source.shape[0])
        print("列数", data_source.shape[1])

    """
    使用pandas读取excel并遍历
    """
    def test_pandas_read_excel(self):
        # 读取时会自动跳过第一行, 是header默认值为0导致的
        # header属性用于指定表头在哪一行, 设置为1将会第三行开始读取, 默认的0从第2行开始读取
        df = pandas.read_excel(r".\data\b.xlsx", sheet_name="Sheet1", header=0)

        max_row_count = df.shape[0]
        max_column_count = df.columns.size

        print("最大行:", max_row_count)
        print("最大列:", max_column_count)

        for i in range(max_row_count):
            row_data = []
            for j in range(max_column_count):
                # 获取每一行中每一列的值
                row_data.append(df.iloc[i, j])
            print(row_data)

    def test_pandas_read_mysql(self):
        # 打开数据库连接
        connect = pymysql.connect(
            host='localhost',
            user='root',
            password='qq991264921',
            database='shiyan'
        )
        df = pandas.read_sql("SELECT * FROM product", connect)
        print(df)
        print(df.shape)

        row_list = df.values.tolist()

        for item in row_list:
            print(item[0], item[1], item[2])

        value = df.get(["id", "name"])
        print("value", value)
        print("value type", type(value))

        connect.close()


if __name__ == '__main__':
    unittest.main()
